package com.example.dao;

import com.example.model.CardInfo;
import com.example.model.TradeInfo;
import com.example.model.UserInfo;
import com.example.util.DBUtil;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import static com.example.util.DBUtil.getConnection;

public class UserDAO {

    // 银行卡登录
    public CardInfo cardLogin(String cardID, String password) {
        CardInfo cardInfo = new CardInfo();

        String sql = "select * from cardInfo where cardID = ?";

        try (Connection conn = getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {

            ps.setString(1, cardID);

            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    if (rs.getString("pass").equals(password)) {
                        cardInfo.setCardId(rs.getString("cardID"));
                        cardInfo.setPass(rs.getString("pass"));
                        cardInfo.setBalance(rs.getBigDecimal("balance"));
                        cardInfo.setOpenMoney(rs.getBigDecimal("openMoney"));
                        cardInfo.setCurId(rs.getString("curId"));
                        cardInfo.setOpenDate(rs.getTimestamp("openDate").toLocalDateTime());
                        cardInfo.setSavingId(rs.getInt("savingId"));
                        cardInfo.setIsReportLoss(rs.getString("IsReportLoss"));
                        cardInfo.setCustomerId(rs.getInt("customerId"));
                        return cardInfo;
                    }
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
        return null;
    }

    // 根据用户ID号找到对应用户(命名错误，应该是getUserInfoByUserID)
    public UserInfo getUserInfoByCarID(int userID) {

        UserInfo userInfo = new UserInfo();

        String sql = "select * from userInfo where customerID = ?";

        try (Connection conn = getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {

            ps.setString(1, String.valueOf(userID));

            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    userInfo.setAddress(rs.getString("address"));
                    userInfo.setCustomerId(rs.getInt("customerId"));
                    userInfo.setCustomerName(rs.getString("customerName"));
                    userInfo.setTelephone(rs.getString("telephone"));
                    userInfo.setPid(rs.getString("pid"));
                }
            }
        }catch(SQLException e) {
            e.printStackTrace();
            return null;
        }
            return userInfo;
    }

    // 存款
    public boolean Saving(String cardID, BigDecimal savingMoney, String depositType){

        String sql = "update cardInfo set balance = balance + ? where cardID = ?";

        try (Connection conn = getConnection();                  // 连接数据库
             PreparedStatement ps = conn.prepareStatement(sql)){        // sql 预处理

            ps.setString(1, savingMoney.toString());    // 替换“？”
            ps.setString(2, cardID);

            ps.executeUpdate();                                         // 执行sql语句

        }catch (SQLException e){
            e.printStackTrace();
            return  false;
        }

        return true;
    }


    public boolean insertTradeinfo(TradeInfo tradeInfo) {
        String sql = " insert into tradeinfo (tradeID,tradeType,cardID,tradeMoney,remark) values(?,?,?,?,?)";
        try (Connection conn = getConnection();                  // 连接数据库
             PreparedStatement ps = conn.prepareStatement(sql)){        // sql 预处理

            ps.setString(1, tradeInfo.getTradeId());    // 替换“？”
            ps.setString(2, tradeInfo.getTradeType());
            ps.setString(3, tradeInfo.getCardId());
            ps.setString(4, tradeInfo.getTradeMoney().toString());
            ps.setString(5, tradeInfo.getRemark());

            ps.executeUpdate();                                         // 执行sql语句

        }catch (SQLException e){
            e.printStackTrace();
            return  false;
        }
        return true;
    }

    // 获取新的银行卡信息
    public CardInfo getCardInfoByCardId(String cardId) {
        // 实现从数据库获取最新银行卡信息的逻辑
        CardInfo cardInfo = new CardInfo();

        String sql = "select * from cardInfo where cardID = ?";

        try (Connection conn = getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {

            ps.setString(1, cardId);

            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    cardInfo.setCardId(rs.getString("cardID"));
                    cardInfo.setPass(rs.getString("pass"));
                    cardInfo.setBalance(rs.getBigDecimal("balance"));
                    cardInfo.setOpenMoney(rs.getBigDecimal("openMoney"));
                    cardInfo.setCurId(rs.getString("curId"));
                    cardInfo.setOpenDate(rs.getTimestamp("openDate").toLocalDateTime());
                    cardInfo.setSavingId(rs.getInt("savingId"));
                    cardInfo.setIsReportLoss(rs.getString("IsReportLoss"));
                    cardInfo.setCustomerId(rs.getInt("customerId"));
                    return cardInfo;
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
        return null;
    }


    // 查询所用的存款类型
    public List<String> getAllDepositType(){
        List<String> depositType = new ArrayList<>();
        String sql = "select savingName from deposit";

        try (Connection conn = getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    depositType.add(rs.getString("savingName"));
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
        return depositType;
    }


    // 取款
    public boolean withdrawal(String cardID, BigDecimal withdrawalMoney){
        String sql = "update cardInfo set balance = balance - ? where cardID = ?";

        try (Connection conn = getConnection();                  // 连接数据库
             PreparedStatement ps = conn.prepareStatement(sql)){        // sql 预处理

            ps.setString(1, withdrawalMoney.toString());    // 替换“？”
            ps.setString(2, cardID);

            ps.executeUpdate();                                         // 执行sql语句

        }catch (SQLException e){
//            e.printStackTrace();
            System.out.println("存款金额出现问题，返回false");
            return  false;
        }

        return true;
    }

    // 转账
    public boolean transferMoney(String transferFromId, String transferToId, BigDecimal transferNumber, String transferToCustomerName) {

        // 先通过cardID找到用户ID，再通过用户ID找到用户信息，核对用户信息后再调用函数转账
        String sql = "select customerID from cardinfo where cardID = ?";
        String customerId = "customerId";
        String userName = "userName";
        try (Connection conn = getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {

            ps.setString(1, transferToId);
            ResultSet rs =  ps.executeQuery();

            if (rs.next()) {
                customerId = rs.getString("customerID");
                userName = getUserInfoByCarID(Integer.parseInt(customerId)).getCustomerName();
            }else {
                return false;
            }

        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }


        if(userName.equals(transferToCustomerName)){
            withdrawal(transferFromId,transferNumber);
            Saving(transferToId, transferNumber,"活期");
        }else {
            return false;
        }

        return true;
    }

    // 挂失
    public boolean reportLoss(String cardID){
        String sql = "update cardInfo set IsReportLoss = '是' where cardID = ?";

        try (Connection conn = getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {

            ps.setString(1, cardID);
            ps.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
        return true;
    }

    // 修改密码
    public boolean changePwd(String cardId, String newPwd){
        String sql = "update cardInfo set pass = ? where cardID = ?";

        try (Connection conn = getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {

            ps.setString(1, newPwd);
            ps.setString(2, cardId);

            ps.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }

        return true;

    }
}